package com.roc.web.action.terminal;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

/**
 * 云商宝终端SN查询
 * @ClassName YsbTermSnQuerySearchCondition
 * @author JD
 * @date 2018年1月10日
 *
 */
public class YsbTermSnQuerySearchCondition extends BaseSearchCondition {
	
	
	private static final long serialVersionUID = 1L;
	private String oneOrgCode;//小机构号
	private String orgCode;
	private String agentId;
	private String posBrand;  //品牌
	private String posType;  //机型
	private String posName; //POS名称
	private String isBind;
	private String isUsed;
	private String serialNum;//
	private String realName;//代理商名称
	private String roleType;//角色类型
//	private String[] agentIdList;//下级代理商

	private String startActDate; // 激活 开始时间(年月日)
	private String endActDate; // 激活 结束时间(年月日)

	private String obj;//代理商id
	private String sfbId;
	private String opId; // 业务员

	private String operators ; // 当前登陆账号
	
	private String merId; // 商户

	private String netInType; // 入网方式

	private String useType; // 是否已刷288
	
	private String ysbIsDirectly;// 是否直属
	
	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		
		return " order by a.dt_create desc";
	}
	@Override
	protected String getRowCountHQL() {
		
		
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT COUNT(*) ");
		sb.append(" FROM T_pos_info i ");
		sb.append(" , T_pos_agent a ");
		sb.append(" , T_OPERATORS o ");		
		if(roleType.equals(Role.AGENT.getCode()) || roleType.equals(Role.SFB_AGENT.getCode()) || roleType.equals(Role.O_MANAGER.getCode())){
			sb.append(	  ", (SELECT T.ID  "  );
			sb.append(     "         FROM T_OPERATORS T " );
			sb.append("       START WITH ID=").append(opId);		    
		    if(roleType.equals(Role.SFB_AGENT.getCode())){
		    	sb.append("	AND T.ROLE_TYPE = '17' " );
		    }else if(roleType.equals(Role.O_MANAGER.getCode())){
		    	sb.append("	AND T.ROLE_TYPE in ('02','03') " );
		    }else{
		    	sb.append("  AND T.ROLE_TYPE = '03' " );
		    }
			sb.append("     CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
			sb.append("      ) TT " );
		}
		sb.append(" where i.serial_num = a.serial_num and i.POS_BRAND = a.POS_BRAND and a.agent_id = o.id ");
		if(roleType.equals(Role.AGENT.getCode()) || roleType.equals(Role.SFB_AGENT.getCode()) || roleType.equals(Role.O_MANAGER.getCode())){ 
			sb.append("and  a.AGENT_ID=TT.ID  " );
		}
		if( roleType.equals(Role.MERCHANT.getCode())){
			sb.append( "and i.MERC_CODE = " ).append(opId);
		}
		if( roleType.equals(Role.WARE_HOUSE_LEAD.getCode())){
			sb.append( "and i.OPERATORS = '" ).append(operators).append("' ");
		}		
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT i.serial_num,i.pos_brand,i.pos_type,i.pos_name,i.org_code,i.is_bind,a.is_used,i.MERC_CODE,i.TERM_CODE,a.REMARK,a.DT_CREATE,a.DT_LASTMOD,o.REAL_NAME,i.tek_id,a.flag ");
		sb.append("  ,(select WM.REMARK2 FROM APSTFR.TBL_TERM_WM_INF WM WHERE WM.MCHT_CD = I.MERC_CODE AND WM.TERM_ID = I.TERM_CODE ) actDate");
		sb.append("  ,( select m.name_busi  from tbl_mcht_inf m where m.mcht_cd = I.MERC_CODE ) name_busi");
		sb.append("  ,( select m.contact_phoneno  from tbl_mcht_inf m where m.mcht_cd = I.MERC_CODE ) contact_phoneno");
		sb.append("  ,( select to_char( m.rec_crt_ts,'yyyyMMdd HH:mm:ss') mchtCrtDate from tbl_mcht_inf m where m.mcht_cd = I.MERC_CODE ) mchtCrtDate ");
		sb.append(" FROM T_pos_info i ");
		sb.append(" , T_pos_agent a ");
		sb.append(" , T_OPERATORS o ");
		if(roleType.equals(Role.AGENT.getCode()) || roleType.equals(Role.SFB_AGENT.getCode()) || roleType.equals(Role.O_MANAGER.getCode())){
			sb.append(	  ", (SELECT T.ID "  );
			sb.append(     "         FROM T_OPERATORS T " );
			sb.append("       START WITH ID=").append(opId);		    
		    if(roleType.equals(Role.SFB_AGENT.getCode())){
		    	sb.append("	AND T.ROLE_TYPE = '17' " );
		    }else if(roleType.equals(Role.O_MANAGER.getCode())){
		    	sb.append("	AND T.ROLE_TYPE in ('02','03') " );
		    }else{
		    	sb.append("  AND T.ROLE_TYPE = '03' " );
		    }
			sb.append("     CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
			sb.append("       ) TT " );
		}
		sb.append(" where i.serial_num = a.serial_num and i.POS_BRAND = a.POS_BRAND and a.agent_id = o.id ");
		if(roleType.equals(Role.AGENT.getCode()) || roleType.equals(Role.SFB_AGENT.getCode()) || roleType.equals(Role.O_MANAGER.getCode())){ 
			sb.append("and  a.AGENT_ID=TT.ID  " );
		}
		if( roleType.equals(Role.MERCHANT.getCode())){
			sb.append( "and i.MERC_CODE = " ).append(opId);
		}
		if( roleType.equals(Role.WARE_HOUSE_LEAD.getCode())){
			sb.append( "and i.OPERATORS = '" ).append(operators).append("' ");
		}		
		return sb.toString(); //y.isBind = 'N'
	}

	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();

		
		if (Constance.isNotEmpty(orgCode)) {
			filedBeanList.add(new WhereHQLFieldBean("i.org_code", orgCode, "=", false));
		}

		if (Constance.isNotEmpty(posBrand) && !posBrand.equals("-1")) {
			filedBeanList.add(new WhereHQLFieldBean("i.pos_brand", posBrand, "=", false));
		}
		if (Constance.isNotEmpty(posType) && !posType.equals("-1")) {
			filedBeanList.add(new WhereHQLFieldBean("i.pos_type", posType, "=", false));
		}
		if (Constance.isNotEmpty(posName) && !posName.equals("-1")) {
			filedBeanList.add(new WhereHQLFieldBean("i.pos_name", posName, "=", false));
		}
		if (Constance.isNotEmpty(agentId)) {
			filedBeanList.add(new WhereHQLFieldBean("a.agent_id", agentId.trim(), "=", false));
		}
		if (Constance.isNotEmpty(isBind) && !isBind.equals("99")) {
			filedBeanList.add(new WhereHQLFieldBean("i.is_bind", isBind, "=", false));
		}
		if (Constance.isNotEmpty(isUsed) && !isUsed.equals("99")) {
			filedBeanList.add(new WhereHQLFieldBean("a.is_used", isUsed, "=", false));
		}
		if (Constance.isNotEmpty(serialNum) && !serialNum.equals("")) {
			filedBeanList.add(new WhereHQLFieldBean("i.serial_num", serialNum.trim(), "=", false));
		}
		if(Constance.isNotEmpty(realName)){
			filedBeanList.add(new WhereHQLFieldBean("o.REAL_NAME", realName.trim(), "like", false,"ALL"));
		}
		if (roleType.equals(Role.AGENT_BUSS.getCode())) {
			filedBeanList.add(new WhereHQLFieldBean("a.AGENT_ID", opId
					.trim().trim(), "=", false));
		}
		
		if (Constance.isNotEmpty(netInType) && !netInType.equals("-1")) {
			String strType = ( netInType.equals("0") ? "0,1" : "2");
			filedBeanList.add(new WhereHQLFieldBean(" i.ASSET_FLAG IN (" + strType + ") "));
		}
		// 是否已做288交易
		if (Constance.isNotEmpty(useType) && !useType.equals("-1")) {
			// 默认 携机入网[2] 不需要扣除 288,所以查询时排除  携机入网 的数据
			filedBeanList.add(new WhereHQLFieldBean(" i.ASSET_FLAG IN (0,1) "));
			// 0--否,1--是
			String strType = ( useType.equals("0") ? " NOT " : "");
			filedBeanList.add(new WhereHQLFieldBean( strType + " EXISTS( SELECT 1 FROM APSTFR.TBL_TERM_WM_INF WM "
					+ " WHERE WM.MCHT_CD = I.MERC_CODE AND WM.TERM_ID = I.TERM_CODE AND WM.REMARK1 IN (0,1) AND WM.STATUS = '01' ) "));
		}

		if (Constance.isNotEmpty(ysbIsDirectly) && !ysbIsDirectly.equals("-1")) {
			
			if ( ysbIsDirectly.equals("0") ){
				filedBeanList.add(new WhereHQLFieldBean("a.AGENT_ID", opId, "=", false));
			} 
			if ( ysbIsDirectly.equals("1") ){
				filedBeanList.add(new WhereHQLFieldBean(" (select o.supri_id from t_operators o where o.id = a.agent_id) ", opId, "=", false));
			}
		}
		if (Constance.isNotEmpty(startActDate) && Constance.isNotEmpty(endActDate)) {
			String startDate = startActDate.replace("-", "");
			String endDate = endActDate.replace("-", "");

			filedBeanList.add(new WhereHQLFieldBean( " EXISTS( SELECT 1 FROM APSTFR.TBL_TERM_WM_INF WM "
					+ " WHERE WM.MCHT_CD = I.MERC_CODE AND WM.TERM_ID = I.TERM_CODE AND WM.REMARK2 >=  " 
					+ startDate + " AND WM.REMARK2 <= " + endDate + " ) "));
		}
		/*
		if(agentIdList!=null&&agentIdList.length >0){//下级代理商
        	filedBeanList.add(new WhereHQLFieldBean("a.agent_id IN("+Constance.getArrayContent(agentIdList)+")"));
        }*/
		
		return filedBeanList;
	}


	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getPosBrand() {
		return posBrand;
	}

	public void setPosBrand(String posBrand) {
		this.posBrand = posBrand;
	}

	public String getPosType() {
		return posType;
	}

	public void setPosType(String posType) {
		this.posType = posType;
	}

	public String getPosName() {
		return posName;
	}

	public void setPosName(String posName) {
		this.posName = posName;
	}

	public String getAgentId() {
		return agentId;
	}

	public void setAgentId(String agentId) {
		this.agentId = agentId;
	}

	public String getIsBind() {
		return isBind;
	}

	public void setIsBind(String isBind) {
		this.isBind = isBind;
	}

	public String getIsUsed() {
		return isUsed;
	}

	public void setIsUsed(String isUsed) {
		this.isUsed = isUsed;
	}

	public String getSerialNum() {
		return serialNum;
	}

	public void setSerialNum(String serialNum) {
		this.serialNum = serialNum;
	}

	public String getRealName() {
		return realName;
	}

	public void setRealName(String realName) {
		this.realName = realName;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getOpId() {
		return opId;
	}

	public void setOpId(String opId) {
		this.opId = opId;
	}

	public String getMerId() {
		return merId;
	}

	public void setMerId(String merId) {
		this.merId = merId;
	}

	public String getSfbId() {
		return sfbId;
	}

	public void setSfbId(String sfbId) {
		this.sfbId = sfbId;
	}

	public String getRoleType() {
		return roleType;
	}

	public void setRoleType(String roleType) {
		this.roleType = roleType;
	}

	public String getOneOrgCode() {
		return oneOrgCode;
	}

	public void setOneOrgCode(String oneOrgCode) {
		this.oneOrgCode = oneOrgCode;
	}

	public String getOperators() {
		return operators;
	}

	public void setOperators(String operators) {
		this.operators = operators;
	}
	public String getNetInType() {
		return netInType;
	}
	public void setNetInType(String netInType) {
		this.netInType = netInType;
	}
	public String getUseType() {
		return useType;
	}
	public void setUseType(String useType) {
		this.useType = useType;
	}
	public String getYsbIsDirectly() {
		return ysbIsDirectly;
	}
	public void setYsbIsDirectly(String ysbIsDirectly) {
		this.ysbIsDirectly = ysbIsDirectly;
	}
	public String getStartActDate() {
		return startActDate;
	}
	public void setStartActDate(String startActDate) {
		this.startActDate = startActDate;
	}
	public String getEndActDate() {
		return endActDate;
	}
	public void setEndActDate(String endActDate) {
		this.endActDate = endActDate;
	}
	
}
